﻿using System;
using System.Text;
using System.Data;
using System.Collections.Generic;
using MySql.Data.MySqlClient;
using Think.Lenovo.Com.Common;
using Think.Lenovo.Com.Models.Users;

namespace Think.Lenovo.Com.DataAccess.User
{
    public class ViewUser
    {
        /// <summary>
        /// 得到一个用户的所有信息
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public ViewUserModel GetViewUserModel(int userId)
        {
            ViewUserModel model = new ViewUserModel();
            #region //

            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;

            #region//sql语句
            StringBuilder sql = new StringBuilder(128);
            sql.Append(" Select * From ")
                .Append(" View_User ")
                .Append(" Where Id=@Id");
            #endregion

            #region//Sql参数数据
            MySqlParameter[] ps = new MySqlParameter[] {
                    new MySqlParameter("@Id",MySqlDbType.Int32)
                };
            ps[0].Value = userId;
            #endregion

            DataSet ds = MysqlHelper.Query(conn, sql.ToString(), ps);
            if (ds != null)
            {
                if (ds.Tables[0] != null)
                {
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        model.Id = Convert.ToInt32(ds.Tables[0].Rows[0]["Id"]);
                        model.UserName = Convert.ToString(ds.Tables[0].Rows[0]["userName"]);
                        model.UserPassword = Convert.ToString(ds.Tables[0].Rows[0]["userPassword"]);
                        model.UserRoleId = Convert.ToInt32(ds.Tables[0].Rows[0]["userRoleId"]);
                        model.UserRoleName = Convert.ToString(ds.Tables[0].Rows[0]["userRoleName"]);
                        model.UserLevelId = Convert.ToInt32(ds.Tables[0].Rows[0]["userLevelId"]);
                        model.UserLevelName = Convert.ToString(ds.Tables[0].Rows[0]["userLevelName"]);
                        model.UserStateId = Convert.ToInt32(ds.Tables[0].Rows[0]["userStateId"]);
                        model.UserStateName = Convert.ToString(ds.Tables[0].Rows[0]["userStateName"]);
                        model.CompanyName = Convert.ToString(ds.Tables[0].Rows[0]["companyName"]);
                        model.RealName = Convert.ToString(ds.Tables[0].Rows[0]["realName"]);
                        model.CompanyRegisteDate = Convert.ToDateTime(ds.Tables[0].Rows[0]["companyRegisteDate"]);
                        model.CompanyAddress = Convert.ToString(ds.Tables[0].Rows[0]["companyAddress"]);
                        model.UserEmail = Convert.ToString(ds.Tables[0].Rows[0]["userEmail"]);
                        model.CompanyEmail = Convert.ToString(ds.Tables[0].Rows[0]["companyEmail"]);
                        model.CompanyTel = Convert.ToString(ds.Tables[0].Rows[0]["companyTel"]);
                        model.UserPhoneNumber = Convert.ToString(ds.Tables[0].Rows[0]["userPhoneNumber"]);
                        model.CompanyRegion = Convert.ToInt32(ds.Tables[0].Rows[0]["companyRegion"]);
                        model.CompanyRegionName = Convert.ToString(ds.Tables[0].Rows[0]["companyRegionName"]);
                        model.RegionUnits = Convert.ToString(ds.Tables[0].Rows[0]["units"]);
                        model.FormatedUserName = Convert.ToString(ds.Tables[0].Rows[0]["formatedUserName"]);
                        model.CompanyGrade = Convert.ToInt32(ds.Tables[0].Rows[0]["companyRegion"]);
                        model.CompanyTypeId = Convert.ToInt32(ds.Tables[0].Rows[0]["companyTypeId"]);
                        model.CompanyTypeName = Convert.ToString(ds.Tables[0].Rows[0]["companyTypeName"]);
                        model.CompanyPropertyId = Convert.ToInt32(ds.Tables[0].Rows[0]["companyPropertyId"]);
                        model.CompanyPropertyName = Convert.ToString(ds.Tables[0].Rows[0]["companyPropertyName"]);
                        model.CompanyPostalCode = Convert.ToString(ds.Tables[0].Rows[0]["companyPostalCode"]);
                        model.CompanyForward = Convert.ToString(ds.Tables[0].Rows[0]["forward"]);
                        model.CompanyFax = Convert.ToString(ds.Tables[0].Rows[0]["companyFax"]);
                        model.CompanyBusinessNumber = Convert.ToString(ds.Tables[0].Rows[0]["companyBusinessNumber"]);
                        model.CompanyRegisteFund = Convert.ToDecimal(ds.Tables[0].Rows[0]["companyRegisteFund"]);
                        model.CompanyProvinceId = Convert.ToInt32(ds.Tables[0].Rows[0]["companyProvinceId"]);
                        model.CompanyCityId = Convert.ToInt32(ds.Tables[0].Rows[0]["CompanyCityId"]);
                        RegionInfo regionInfo = new RegionInfo();
                        #region//得到当用户所属区域名称
                        model.CompanyRegionName = regionInfo.GetModel(model.CompanyRegion).NodeName;
                        #endregion
                        #region//得到当前用户所属省的名称
                        model.CompanyProvinceName = regionInfo.GetModel(model.CompanyProvinceId).NodeName;
                        #endregion
                        #region//得到当前用户所属城市名称
                        model.CompanyCityName = regionInfo.GetModel(model.CompanyCityId).NodeName;
                        #endregion
                    }
                }
            }
            return model;

            #endregion
        }

        /// <summary>
        /// 得到一页数据
        /// </summary>
        /// <param name="pageIndex">页号</param>
        /// <param name="pageSize">每页多少数据</param>
        /// <returns></returns>
        public LinkedList<ViewUserModel> GetViewUsers(int pageIndex, int pageSize)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
            StringBuilder sql = new StringBuilder(512);
            if (pageIndex < 1)
            {
                pageIndex = 1;
            }
            if (pageSize < 1)
            {
                pageSize = 10;
            }
            sql.Append("Select * from view_user limit ")
                .Append((pageIndex - 1) * pageSize)
                .Append(",")
                .Append(pageSize);
            DataSet ds = MySqlHelper.ExecuteDataset(conn, sql.ToString());
            LinkedList<ViewUserModel> list = new LinkedList<ViewUserModel>();
            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {

                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        DateTime dt;
                        Int32 i;
                        Decimal d;
                        ViewUserModel model = new ViewUserModel();
                        model.Id = Convert.ToInt32(row["Id"]);
                        model.UserName = Convert.ToString(row["userName"]);
                        model.UserPassword = Convert.ToString(row["userPassword"]);
                        model.UserRoleId = Convert.ToInt32(row["userRoleId"]);
                        model.UserRoleName = Convert.ToString(row["userRoleName"]);
                        model.UserLevelId = Convert.ToInt32(row["userLevelId"]);
                        model.UserLevelName = Convert.ToString(row["userLevelName"]);
                        model.UserStateId = Convert.ToInt32(row["userStateId"]);
                        model.UserStateName = Convert.ToString(row["userStateName"]);
                        model.CompanyName = Convert.ToString(row["companyName"]);
                        model.RealName = Convert.ToString(row["realName"]);

                        DateTime.TryParse(row["companyRegisteDate"].ToString(), out dt);
                        model.CompanyRegisteDate = dt;
                        model.CompanyAddress = Convert.ToString(row["companyAddress"]);
                        model.UserEmail = Convert.ToString(row["userEmail"]);
                        model.CompanyEmail = Convert.ToString(row["companyEmail"]);
                        model.CompanyTel = Convert.ToString(row["companyTel"]);
                        model.UserPhoneNumber = Convert.ToString(row["userPhoneNumber"]);
                        Int32.TryParse(row["companyRegion"].ToString(), out i);
                        model.CompanyRegion = i;
                        model.CompanyRegionName = Convert.ToString(row["companyRegionName"]);
                        model.RegionUnits = Convert.ToString(row["units"]);
                        model.FormatedUserName = Convert.ToString(row["formatedUserName"]);
                        Int32.TryParse(row["companyRegion"].ToString(), out i);
                        model.CompanyGrade = i;
                        Int32.TryParse(row["companyTypeId"].ToString(), out i);
                        model.CompanyTypeId = i;
                        model.CompanyTypeName = Convert.ToString(row["companyTypeName"]);
                        Int32.TryParse(row["companyPropertyId"].ToString(), out i);
                        model.CompanyPropertyId = i;
                        model.CompanyPropertyName = Convert.ToString(row["companyPropertyName"]);
                        model.CompanyPostalCode = Convert.ToString(row["companyPostalCode"]);
                        model.CompanyForward = Convert.ToString(row["forward"]);
                        model.CompanyFax = Convert.ToString(row["companyFax"]);
                        model.CompanyBusinessNumber = Convert.ToString(row["companyBusinessNumber"]);
                        Decimal.TryParse(row["companyRegisteFund"].ToString(), out d);
                        model.CompanyRegisteFund = d;

                        RegionInfo regionInfo = new RegionInfo();
                        #region//得到当用户所属区域名称
                        model.CompanyRegionName = regionInfo.GetModel(model.CompanyRegion).NodeName;
                        #endregion
                        #region//得到当前用户所属省的名称
                        model.CompanyProvinceName = regionInfo.GetModel(model.CompanyProvinceId).NodeName;
                        #endregion
                        #region//得到当前用户所属城市名称
                        model.CompanyCityName = regionInfo.GetModel(model.CompanyCityId).NodeName;
                        #endregion
                        list.AddLast(model);
                    }
                }
            }
            return list;
        }


        public LinkedList<ViewUserModel> GetViewUsers(int pageIndex, int pageSize,int currentRegionId)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
            StringBuilder sql = new StringBuilder(512);
            if (pageIndex < 1)
            {
                pageIndex = 1;
            }
            if (pageSize < 1)
            {
                pageSize = 10;
            }
            sql.Append("Select * from view_user")
                .Append(" Where companyRegion=")
                .Append(currentRegionId)
                .Append(" And userRoleId<>1")
                .Append(" limit ")
                .Append((pageIndex - 1) * pageSize)
                .Append(",")
                .Append(pageSize);
            DataSet ds = MySqlHelper.ExecuteDataset(conn, sql.ToString());
            LinkedList<ViewUserModel> list = new LinkedList<ViewUserModel>();
            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {

                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        DateTime dt;
                        Int32 i;
                        Decimal d;
                        ViewUserModel model = new ViewUserModel();
                        model.Id = Convert.ToInt32(row["Id"]);
                        model.UserName = Convert.ToString(row["userName"]);
                        model.UserPassword = Convert.ToString(row["userPassword"]);
                        model.UserRoleId = Convert.ToInt32(row["userRoleId"]);
                        model.UserRoleName = Convert.ToString(row["userRoleName"]);
                        model.UserLevelId = Convert.ToInt32(row["userLevelId"]);
                        model.UserLevelName = Convert.ToString(row["userLevelName"]);
                        model.UserStateId = Convert.ToInt32(row["userStateId"]);
                        model.UserStateName = Convert.ToString(row["userStateName"]);
                        model.CompanyName = Convert.ToString(row["companyName"]);
                        model.RealName = Convert.ToString(row["realName"]);

                        DateTime.TryParse(row["companyRegisteDate"].ToString(), out dt);
                        model.CompanyRegisteDate = dt;
                        model.CompanyAddress = Convert.ToString(row["companyAddress"]);
                        model.UserEmail = Convert.ToString(row["userEmail"]);
                        model.CompanyEmail = Convert.ToString(row["companyEmail"]);
                        model.CompanyTel = Convert.ToString(row["companyTel"]);
                        model.UserPhoneNumber = Convert.ToString(row["userPhoneNumber"]);
                        Int32.TryParse(row["companyRegion"].ToString(), out i);
                        model.CompanyRegion = i;
                        model.CompanyRegionName = Convert.ToString(row["companyRegionName"]);
                        model.RegionUnits = Convert.ToString(row["units"]);
                        model.FormatedUserName = Convert.ToString(row["formatedUserName"]);
                        Int32.TryParse(row["companyRegion"].ToString(), out i);
                        model.CompanyGrade = i;
                        Int32.TryParse(row["companyTypeId"].ToString(), out i);
                        model.CompanyTypeId = i;
                        model.CompanyTypeName = Convert.ToString(row["companyTypeName"]);
                        Int32.TryParse(row["companyPropertyId"].ToString(), out i);
                        model.CompanyPropertyId = i;
                        model.CompanyPropertyName = Convert.ToString(row["companyPropertyName"]);
                        model.CompanyPostalCode = Convert.ToString(row["companyPostalCode"]);
                        model.CompanyForward = Convert.ToString(row["forward"]);
                        model.CompanyFax = Convert.ToString(row["companyFax"]);
                        model.CompanyBusinessNumber = Convert.ToString(row["companyBusinessNumber"]);
                        Decimal.TryParse(row["companyRegisteFund"].ToString(), out d);
                        model.CompanyRegisteFund = d;

                        RegionInfo regionInfo = new RegionInfo();
                        #region//得到当用户所属区域名称
                        model.CompanyRegionName = regionInfo.GetModel(model.CompanyRegion).NodeName;
                        #endregion
                        #region//得到当前用户所属省的名称
                        model.CompanyProvinceName = regionInfo.GetModel(model.CompanyProvinceId).NodeName;
                        #endregion
                        #region//得到当前用户所属城市名称
                        model.CompanyCityName = regionInfo.GetModel(model.CompanyCityId).NodeName;
                        #endregion
                        list.AddLast(model);
                    }
                }
            }
            return list;
        }


        /// <summary>
        /// 得到用户数量
        /// </summary>
        /// <returns></returns>
        public int GetUserCount()
        {
            #region //

            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;

            #region//sql语句
            StringBuilder sql = new StringBuilder(128);
            sql.Append(" Select count(Id) From ")
                .Append(" View_User ");
            #endregion

            Object obj = MysqlHelper.ExecuteScalar(conn, sql.ToString());
            return obj == null ? 0 : Convert.ToInt32(obj);

            #endregion
        }

        public int GetUserCount(int currentRegionId)
        {
            #region //

            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
            RegionInfo region = new RegionInfo();
            int bigAreaId = region.GetBigAreaByCityId(currentRegionId).Id;
            #region//sql语句
            StringBuilder sql = new StringBuilder(128);
            sql.Append(" Select count(Id) From ")
                .Append(" View_User Where companyRegion=" + currentRegionId)
                .Append(" And userRoleId<>1");
            #endregion

            Object obj = MysqlHelper.ExecuteScalar(conn, sql.ToString());
            return obj == null ? 0 : Convert.ToInt32(obj);

            #endregion
        }
    }
}
